package com.lee.sql;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.lang.Singleton;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.lee.exception.SyncException;
import com.lee.meta.ColumnMeta;
import com.lee.meta.IndexColumnMeta;
import com.lee.meta.IndexMeta;
import com.lee.meta.TableMeta;
import com.lee.type.IndexTypeEnum;

import java.sql.Types;
import java.util.Comparator;
import java.util.List;
import java.util.Map;

/**
 * @description: MySQL sql
 * @author: 青石路
 * @date: 2024/4/26 15:20
 */
public class MysqlSql implements DBSql {

    // 使用 volatile 关键字保证可见性和禁止指令重排序
    private static volatile MysqlSql instance;

    // 构造方法私有化，防止外部创建实例
    private MysqlSql() {}

    // 提供一个全局访问点，使用双重检查锁定保证线程安全
    public static MysqlSql getInstance() {
        if (instance == null) {
            synchronized (Singleton.class) {
                if (instance == null) {
                    instance = new MysqlSql();
                }
            }
        }
        return instance;
    }

    @Override
    public String getTableColumnSql(TableQueryParam queryParam) {
        return StrUtil.format("SELECT * FROM {}.{} WHERE 1=0", queryParam.getCatalog(), queryParam.getTableName());
    }

    @Override
    public String getMaxValueSql(TableQueryParam queryParam, String columnName) {
        return StrUtil.format("SELECT MAX({}) FROM {}.{}", columnName, queryParam.getCatalog(), queryParam.getTableName());
    }

    @Override
    public String getTableDDLSql(TableQueryParam queryParam) {
        return StrUtil.format("SHOW CREATE TABLE {}.{} ", queryParam.getCatalog(), queryParam.getTableName());
    }

    @Override
    public String getCreateTableSql(TableQueryParam queryParam, TableMeta tableMeta, List<ColumnMeta> columnMetas,
                                    IndexMeta primaryKeyMeta, Map<String, IndexMeta> indexMetaMap) {
        StringBuilder createSql = new StringBuilder("CREATE TABLE " + queryParam.getTargetCatalog() + "." + queryParam.getTargetTableName() + " ( ");
        for (ColumnMeta columnMeta : columnMetas) {
            createSql.append(columnMeta.getColumnName()).append(" ").append(getColumnType(columnMeta));
            if (columnMeta.getIfUnsigned()) {
                createSql.append(" UNSIGNED");
            }
            if (columnMeta.getIfNullable() == 0) {
                createSql.append(" NOT NULL");
            }
            if (StrUtil.isNotBlank(columnMeta.getDefaultValue())) {
                createSql.append(" DEFAULT '").append(columnMeta.getDefaultValue()).append("'");
            }
            if (columnMeta.getIfAutoIncrement()) {
                createSql.append(" AUTO_INCREMENT");
            }
            if (StrUtil.isNotBlank(columnMeta.getRemarks())) {
                createSql.append(" COMMENT '").append(columnMeta.getRemarks()).append("'");
            }
            createSql.append(",");
        }
        // 主键处理
        if (ObjectUtil.isNotNull(primaryKeyMeta)) {
            List<IndexColumnMeta> indexColumns = primaryKeyMeta.getIndexColumns();
            indexColumns.sort(Comparator.comparingInt(IndexColumnMeta::getOrdinalPosition));
            createSql.append(" PRIMARY KEY (");
            for (int i=0; i<indexColumns.size(); i++) {
                if (i>0) {
                    createSql.append(",");
                }
                createSql.append(indexColumns.get(i).getColumnName());
            }
            createSql.append("),");
        }
        if (CollectionUtil.isNotEmpty(indexMetaMap)) {
            for (IndexMeta indexMeta : indexMetaMap.values()) {
                if (indexMeta.getIndexType() == IndexTypeEnum.UNIQUE) {
                    // 唯一索引
                    createSql.append("UNIQUE ");
                }
                createSql.append("KEY ").append(indexMeta.getIndexName()).append(" (");
                List<IndexColumnMeta> indexColumns = indexMeta.getIndexColumns();
                indexColumns.sort(Comparator.comparingInt(IndexColumnMeta::getOrdinalPosition));
                for (int i=0; i<indexColumns.size(); i++) {
                    if (i>0) {
                        createSql.append(",");
                    }
                    createSql.append(indexColumns.get(i).getColumnName());
                }
                createSql.append("),");
            }
        }
        // 删除最后一个逗号
        createSql.deleteCharAt(createSql.length()-1);
        createSql.append(")");
        if (StrUtil.isNotBlank(tableMeta.getRemarks())) {
            createSql.append(" COMMENT='").append(tableMeta.getRemarks()).append("'");
        }
        return createSql.toString();
    }

    /**
     * 获取表 字段类型
     * @param columnMeta 列元数据
     * @return mysql 字段类型
     */
    private String getColumnType(ColumnMeta columnMeta) {
        switch (columnMeta.getColumnType()) {
            // 数值类型
            case Types.TINYINT:
                return "TINYINT";
            case Types.SMALLINT:
                return "SMALLINT";
            case Types.INTEGER:
                return "INT";
            case Types.BIGINT:
                return "BIGINT";
            case Types.FLOAT:
            case Types.REAL:
                return columnMeta.getPrecision() > 0 ? "FLOAT(" + columnMeta.getPrecision() + "," + columnMeta.getScale() + ")" : "FLOAT";
            case Types.DOUBLE:
                return columnMeta.getPrecision() > 0 ? "DOUBLE(" + columnMeta.getPrecision() + "," + columnMeta.getScale() + ")" : "DOUBLE";
            case Types.DECIMAL:
                return "DECIMAL(" + columnMeta.getPrecision() + "," + columnMeta.getScale() + ")";
            case Types.NUMERIC:
                return columnMeta.getScale() <= 0 ? "BIGINT" : "DECIMAL(" + columnMeta.getPrecision() + "," + columnMeta.getScale() + ")";
            // 字符与字符串类型
            case Types.CHAR:
            case Types.NCHAR:
                return columnMeta.getPrecision() > 0 ? "CHAR(" + columnMeta.getPrecision() + ")" : "CHAR";
            case Types.VARCHAR:
            case Types.NVARCHAR:
                return columnMeta.getPrecision() > 0 ? "VARCHAR(" + columnMeta.getPrecision() + ")" : "VARCHAR";
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR:
                switch (columnMeta.getColumnTypeName()) {
                    case "TINYTEXT":
                        return "TINYTEXT";
                    case "MEDIUMTEXT":
                        return "MEDIUMTEXT";
                    case "LONGTEXT":
                        return "LONGTEXT";
                    case "JSON":
                        return "JSON";
                    default:
                        return "TEXT";
                }
            case Types.CLOB:
            case Types.NCLOB:
                return "LONGTEXT";
            // 日期和时间类型
            case Types.DATE:
                switch (columnMeta.getColumnTypeName()) {
                    case "YEAR":
                        return "YEAR";
                    default:
                        return "DATE";
                }
            case Types.TIME:
                return "TIME" + (columnMeta.getPrecision() > 8 ? "(" + (columnMeta.getPrecision() - 9) + ")" : "");
            case Types.TIMESTAMP:
                switch (columnMeta.getColumnTypeName()) {
                    case "DATETIME":
                        return "DATETIME" + (columnMeta.getPrecision() > 19 ? "(" + (columnMeta.getPrecision() - 20) + ")" : "");
                    case "DATE":
                        // oracle 的 DATE
                        return "DATETIME";
                    default:
                        return "TIMESTAMP"+ (columnMeta.getPrecision() > 19 ? "(" + (columnMeta.getPrecision() - 20) + ")" : "");
                }
            // 二进制类型
            case Types.BIT:
            case Types.BOOLEAN:
                return columnMeta.getPrecision() > 0 ? "BIT(" + columnMeta.getPrecision() + ")" : "BIT";
            case Types.BINARY:
                return columnMeta.getPrecision() > 0 ? "BINARY(" + columnMeta.getPrecision() + ")" : "BINARY";
            case Types.VARBINARY:
                return columnMeta.getPrecision() > 0 ? "VARBINARY(" + columnMeta.getPrecision() + ")" : "VARBINARY";
            case Types.BLOB:
            case Types.LONGVARBINARY:
                switch (columnMeta.getColumnTypeName()) {
                    case "TINYBLOB":
                        return "TINYBLOB";
                    case "MEDIUMBLOB":
                        return "MEDIUMBLOB";
                    case "LONGBLOB":
                        return "LONGBLOB";
                    default:
                        return "BLOB";
                }
            case Types.OTHER:
                if (columnMeta.getColumnTypeName().contains("VARCHAR")) {
                    return "VARCHAR" + (columnMeta.getPrecision() > 0 ? "(" + columnMeta.getPrecision() + ")" : "");
                } else if (columnMeta.getColumnTypeName().contains("TIMESTAMP")) {
                    return "TIMESTAMP" + (columnMeta.getScale() > 0 ? "(" + columnMeta.getScale() + ")" : "");
                }
                else {
                    throw new SyncException("不支持的类型：" + columnMeta.getColumnTypeName());
                }
            default:
                throw new SyncException("不支持的类型：" + columnMeta.getColumnTypeName());
        }
    }
}
